﻿using System;
using System.Data;
using VegetableSale.NET.Entities;
using VegetableSale.NET.Utils;

namespace VegetableSale.NET.Dao;

public class SaleMapper
{
    public List<Sale> getSaleList(QueryDTO queryDTO)
    {
        String sql = "";

        if (queryDTO.Keyword.Equals(""))
        {
            sql = "select * from sale;";
        }
        else
        {
            sql = "SELECT * FROM sale WHERE custid='" + queryDTO.Keyword + "';";
        }

        List<Sale> sales = new List<Sale>();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            uint id = (uint) row[0];
            int custid = (int) row[1];
            int commid = (int) row[2];
            int salemount = (int) row[3];
            int salemoney = (int) row[4];
            double allmoney = (double) row[5];
            DateTime saletime = (DateTime) row[6];

            Sale sale = new Sale(id, custid, commid, salemount, salemoney, allmoney, saletime);
            sales.Add(sale);
        }

        return sales;
    }


    public List<Gooden> saleAllGood()
    {
        string sql = "select * from good;";

        List<Gooden> goods = new List<Gooden>();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            uint commid = (uint) row[0];
            string commname = (string) row[1];
            double commoney = (double) row[2];
            string commdesc = (string) row[3];
            int providerid = (int) row[4];
            string commbering = (string) row[5];
            string size = (string) row[6];
            double inventory = (double) row[7];
            string picture = (string) row[8];
            Gooden good = new Gooden(commid, commname, commoney, commdesc, providerid, commbering, size, inventory,
                picture, "生鲜");
            goods.Add(good);
        }

        return goods;
    }

    public List<Object> getSaleMountById(int id)
    {
        string sql = "SELECT commid,salemount FROM sale WHERE id=" + id + ";";

        int saleMount = 0;
        
        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        List<Object> returnList = new List<object>();

        foreach (DataRow row in dataTable.Rows)
        {
            int commid = (int) row[0];
            saleMount = (int) row[1];
            returnList.Add(commid);
            returnList.Add(saleMount);
        }

        return returnList;
    }
    
    public void deleteSale(int id)
    {
        string sql = "DELETE FROM sale WHERE id=" + id + ";";

        MySqlHelper.ExecuteSql(sql);
    }

    public void addSale(SaleQuery saleQuery)
    {
        double allmoney = saleQuery.Salemoney * saleQuery.Salemount;
        string saletime = TimeTransferUtils.GetSqlServerTimeString();
        string sql = "INSERT INTO sale (custid,commid,salemount,salemoney,allmoney,saletime) VALUES (" +
                     saleQuery.Custid + "," + saleQuery.Commid + "," + saleQuery.Salemount + "," + saleQuery.Salemoney +
                     "," + allmoney + ",'" + saletime + "');";

        MySqlHelper.ExecuteSql(sql);
    }

    public Sale getSaleById(int Id)
    {
        
        string sql = "SELECT * FROM sale WHERE id="+Id+";";
     
        Sale sale = new Sale();

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        foreach (DataRow row in dataTable.Rows)
        {
            uint id = (uint) row[0];
            int custid = (int) row[1];
            int commid = (int) row[2];
            int salemount = (int) row[3];
            int salemoney = (int) row[4];
            double allmoney = (double) row[5];
            DateTime saletime = (DateTime) row[6];

            sale.Id = id;
            sale.Custid = custid;
            sale.Commid = commid;
            sale.Salemount = salemount;
            sale.Salemoney = salemoney;
            sale.Allmoney = allmoney;
            sale.Saletime = saletime;
        }
        return sale;
    }

    public void updateSale(SaleUpdateQuery saleUpdateQuery)
    {
        double allmoney = saleUpdateQuery.Salemoney * saleUpdateQuery.Salemount;
        string saletime = TimeTransferUtils.GetSqlServerTimeString();
        string sql = "UPDATE sale SET custid=" + saleUpdateQuery.Custid + ",commid=" + saleUpdateQuery.Commid +
                     ",salemount=" + saleUpdateQuery.Salemount + ",salemoney=" + saleUpdateQuery.Salemoney +
                     ",allmoney=" + allmoney + ",saletime='" + saletime + "' WHERE id=" + saleUpdateQuery.Id + ";";

        MySqlHelper.ExecuteSql(sql);
    }
    
    public void updateSaleMount(int id,int newSaleMount,double allmoney)
    {
        string sql = "UPDATE sale SET salemount=" + newSaleMount + ",allmoney="+allmoney+" WHERE id=" + id + ";";

        MySqlHelper.ExecuteSql(sql);
    }

    public double getInventoryById(int commid)
    {
        string sql = "SELECT inventory FROM good WHERE commid=" + commid + ";";

        DataSet dateSet = MySqlHelper.GetDataSet(sql);
        DataTable dataTable = dateSet.Tables[0];

        double inventory=0;

        foreach (DataRow row in dataTable.Rows)
        {
            inventory = (double) row[0];
        }

        return inventory;
    }
}
